Dear Sir, Sergei Golubchik gives me this e-mail address telling me that you can help me about a project that my company is developing.
we need to develope a J2SE project that works on N MYSQL 4.0.20 servers installed on the same machine. Now I try to explain my problem. The Hardware, actually, is a normal PC: CPU AMD ATHLON XP 2000+ RAM 770Mb DDR 2 * HD SCSI 160Mb/s. With a Red Hat Linux 9.B installed. Now (the beginning) I use only 2 MYSQL server, one for SCSI disk. The two MYSQL servers are totally indipendent. In either of these, I have created a database, and a MYISAM table. The size of one record is 30 bytes and there is only an index made by two fields (varchar(8) and varchar(10)). I need to insert in this table-structrure an amount of data that have to be, at least, the size of 35.000 records for second, 24 hours for day, 31 days for month. I reached, using only one MYSQL server, 20.000 records for second. Installing two services I hoped that I could reach 40.000 records for second (or some minus) because MySQL servers should work in paraller mode. But it isn't the truth, the speed doesn't increment. the J2SE project use multithreading technology, and is like a MySQL servers load-balancer. Looking at the performance (with top), I see that the CPU never goes over 40%, and the RAM is about 40%. These performance are similar using only one MySQL server or two MySQL servers. I tried to correctly set MySQL system variables, I also tried to do an higher priority to MySQL server processes, but the performance are the same. Another strange thing is the thread_concurrencing running on MySQL servers, looking at the status, threads running value never oltrepass 2, and I set 8 thread_concurrency. Now I explain you how the final project should be: there is a J2SE software that runs as a daemon, it listen to a TCP port. this daemon is connected (as a client with JConnector 3.0.14) to N MySQL servers on a DBServer (at least 2 MYSQL services). the DBServer will be a very powerful server (because my customer needs more than 35.000 records for second and many clients connected to the daemon). A client connected to the daemon can do usually two things: asks the daemon to do insert data in database, or/and read data from database. (So the daeamon uses only SELECT .. FROM ... WHERE ... LIMIT ... UNION and INSERT INTO ... VALUES (),(),()). The client data requests are sent to the daemon that launchs queries on the N MySQL Servers (as a balancer) and eventually gives the replies. The data have to be in real time (so I can't use INSERT DELAYED). the speed of the inserts have to be maximum as possible (at least 35.000 records for second). During the insert operations another clients, in the same moment, could ask the daemon to give him the data inserted in a period. usually the data have about a size of 70Mb (1 day data), and the maximum request is to give data of one entire month. More Clients can do these types of requests. Obviously I will create 4 tables for a day for either MySQL service. Now I don't know if I'll use MySQL commands (MERGE, RAID or others) or I'll do all from my daemon (I have to do test to take this decision). This is all.... In your MySQL doc, I read that you have tested MySQL for a big Sweden company, but you have not mentioned anything about the size of the data that this company store, the clients connected, the size of the queries or the number of inserts. First I would like to know if MySQL could reach these high performance... Because my actually problem is the Hardware resource that aren't full using my little PC. Why have I to buy a powerful server If this hardware isn't full used? The bottleneck isn't in the SCSI disk (I installed MYSQL servers on RAMDISK partitions with the same results). The bottleneck I think is MySQL, but I also think that it is well configured. Here is my.cnf file (I have two equals configurations files, one for MySQL server) port = 330[0-1] socket = /ptolle/ssc/db/mysql_[0-1].sock key_buffer = 256M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 70M read_buffer_size = 70M query_cache_size = 70M thread_cache = 8 thread_concurrency = 8 ---> no more than 2 threads run!!! max-connections = 200 skip-innodb Finally I tried also to compile the MySQL tarball with the gcc (the version is 3.2.2-5) compiler parameters that are mentioned in your documentation without better results. have you got any parameters that limit MySQL performance (in the header libraries or others)? or have you got some informations about MySQL configuration that could speed up MySQL or bring MySQL services to work in paraller mode and use all hardware resource avalaible? Thank you very much. Best Regards Alfredo Marchini Developer Manager SysNet Solutions S.r.l. Via Rimini, 49 - Centro Dir. Leonardo da Vinci 59100 Prato (PO) Tel. 0574/484822 - Fax 0574/448075 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
